Expression Editor

The Expression Editor is used to create formulas and other calculated expressions. It is available to use in many types of input fields; when it is available the Expressions button on the ribbon will become active.

expression editor

You can use a variety of data as function arguments. The available data are listed in different tabs depending on their type. The Periodic tab lists periodic variables. The Scalar tab lists scalar-type variables. The Well tab allows you to use production forecasts from the Decline workspace. The System tab lists other items such as case and scenario names, period, and node type.

For version 16.5 Update 7 and later: The System function 'Unique ID' can be used to access a document's Unique ID value (this ID is shared across all Versions).

Use the Validate button to test whether the expression has valid syntax.

Use the Clear Expression button to delete the edited expression completely.

The list of available functions includes standard mathematical and statistical functions, as well as custom functions specific to Dataflow. When you select a function from the list, its description is displayed in the Function Description box, including the required arguments.

Function descriptions

Note: basic mathematical functions (ABS, ATAN, AVERAGE, CEILING, COS, COSH, EXP, FLOOR, INTPOWER, LN, LOG, LOG10, MAX, MIN, MOD, POWER, RAND, ROUND, SIGN, SIN, SINH, SQRT, SUM, TAN, TANH, TRUNC), text manipulation functions (CONCATENATE, FLOAT, INSTR, LEN, LOWER, LTRIM, MID, RTRIM, STRLIKE, TEXT, TRIM, UPPER), and logic functions (IF, ISNULL) are not included in the following table.

Function	Description
CONVERTTOREAL(variable, [currency])	Returns the values of the periodic numeric variable in real terms. variable: a periodic numeric variable currency: an optional parameter for currency code. This should only be used if either the input or output variable is not set to default currency and should be set to the expected output currency Example: If template currency is USD, and scalar variable is in EUR, the expression would have these parameters: CONVERTTOREAL([Var1], "EUR")
CUMULATIVE(periodic_variable)	Gets the cumulative periodic values of the given periodic variable. Example: If Var1=1,2,3,4,5, CUMULATIVE([Var1]) = 1,3,6,10,15
CUMULATIVERANGE(variable, start, end)	Calculates the cumulative range value of a periodic variable: variable: periodic variable start: range start period index or date end: range end period index or date Example: If Var1=1,2,3,4,5 over a period 2010 to 2014. Range specified by integers: CUMULATIVERANGE([Var1],1,3) = null,2,5,9,null Range specified by date: CUMULATIVERANGE([Var1],"1/1/2011","12/1/2013") = null,2,5,9,null
DATE(year,month,day)	Generates a date representation from the input year, month and day. This type of date is independent of the operating system regional setting and date format.
DATEADD(datepart, number, date)	Returns the result date when a specified integer number of intervals is added to one part of the input date. datepart: the part of the date to which an integer number will be added. The allowed values are y, m, d, for year, month, day, respectively number: an expression that can be resolved to an integer which will be added to datepart date: an expression that can be resolved to a date. This can either be a date variable, a string variable that can be converted to a date, or text that can be converted to a date Example: DATEADD("y", 1, "1 January 2010") gives the result "1 January 2011". (Note: This function will not evaluate if the resultant date is less than 1 January 0001 or greater than 31 December 9999.)
DATECOMPARE(date1, date2)	Compares the two input dates and returns: -1 if date1 > date2 0 if date1 = date2 1 if date1 < date2 date1 and date2 can be date variables or text values. Text values will be converted to dates using the operating system regional settings. Example: DATECOMPARE("1 January 2000", "1 January 2010") returns 1. DATECOMPARE("1 January 2010", "1 January 2000") returns -1
DATEPART(date, datepart)	Extracts the specified part of an input date and returns a number. date: a date variable or a text value. A text value will be converted to a date using the operating system regional settings datepart: y for Year, m for Month, or d for Day. Example: DATEPART("31/12/2006", "y") returns the value 2006.
DATETEXT(date, [format])	Converts the input date to text. If the optional format argument is supplied, the date will be formatted using that format, otherwise the operating system regional settings will be followed. Examples: DATETEXT([DateVariable1]), DATETEXT([DateVariable1],""MM/dd/yyyy""). Format specifiers: d: day 1 - 31 dd: day 01 - 31 M: month 1 - 12 MM: month 01 - 12 MMM: abbreviated month name (e.g. Jan, Feb) MMMM: full month name yy: year 00 - 99 yyyy: 4-digit year
DATETONUMERIC(date)	Converts the input date to a numeric value using the 'OLE Automation Date' format (i.e., the number of days after or before midnight on 30 December 1899). Example: DATETONUMERIC("1 January 1900") returns the value 2.
DEFERREDRATE(production, deferment)	Outputs a periodic variable of deferred production rates for future production, based on the input production variable and deferment schedule. production: periodic variable for production rate. deferment: periodic variable which represents the deferment percentages.
FIRSTNONZERODATE(periodic_variable)	Finds the first non-zero value of the input periodic variable and returns the date of the period in which it occurs. Returns null if no non-zero values are found.
FIRSTNONZEROPERIODINDEX(periodic_variable)	Finds the first non-zero value of the input periodic variable and returns the zero-based index of the period in which it occurs. Returns -1 if a non-zero value is not found.
GETCASENAME(current_cases, category)	Returns the case name parsed from the input current_cases argument, for the input category. current_cases: text descriptor of the current scenario's cases, e.g., the '[Current Cases]' system variable. category: a variable category, e.g., 'Production', 'Capital', 'Opcosts'.
GETHIERARCHYPART([Hierarchy Location], part_id)	Returns the hierarchy part parsed from the input hierarchy location, according to the input part identifier. part_id can be either: the hierarchy level (0 = root node, 1 = first level, etc.), a level name (e.g., "Country", "Region"), or a special identifier ("_last" = last node, "_parent" = parent of last node). Examples: if the input hierarchy node is 'Atlantis/Atlantis UK/North Sea': GETHIERARCHYPART([Hierarchy Location],2) returns the name of the node at level 2 in the hierarchy path, i.e. 'North Sea' GETHIERARCHYPART([Hierarchy Location],"Company") returns the name of the Company level node in the hierarchy path, 'Atlantis UK' GETHIERARCHYPART([Hierarchy Location],"_parent") returns the parent node of the last node, 'Atlantis UK'
GETHIERARCHYPATH([Hierarchy Location])	Using the system variable [Hierarchy Location] as input, this function returns the path portion of the location. Example: Given a node that is 3 levels down, GETTHEHIERARCHYPATH ([Hierarchy Location]) will return a text representation of the names of all the nodes to traverse to get to the current node, e.g. "Root Node\Parent Node\Current Node"
GETLISTPROPERTY(listvariable, property, [default])	Returns a property value from a list which is assigned to a variable within a template. listvariable: scalar variable for the assigned list. property: the list property for the required return value. default: optional input to specify a text string which will be used as the function return value if a match is not found for the specified list and property. Example: A list is created to store a set of country names with extra properties for notes (note) and country codes (code). The list is then assigned to the variable CountryInfo within a template. 1. GETLISTPROPERTY("CountryInfo","note") gets the value of note for the currently-selected country 2. GETLISTPROPERTY("CountryInfo","code") gets the value of code for the currently-selected country 3. GETLISTPROPERTY("CountryInfo", "code", "GB") returns the value 'GB' if a match is not found for the given list and property.
GETPRICESCENARIODATA(price, scenario)	Returns the periodic data for a product stream's linked price, for the input scenario. price: the price linked to the product (to be selected from the list of prices displayed in the Price tab under Function Arguments). scenario: a price deck scenario name. Example: GETPRICESCENARIODATA([Oil 1 Price],"High")
GETSCENARIODATA(scenario, variable)	Returns data for the specified variable and scenario. scenario: a scenario name. variable: a scalar or periodic variable. For version 16.5 Update 13 and later: the function has an optional third parameter GETSCENARIODATA(scenario,variable,[default]); if the requested scenario does not exist in the document then the default value will be returned. Examples: 1. GETSCENARIODATA("1C",[Production.Oil]): gets the value of variable 'Production.Oil' for scenario '1C' 2. GETSCENARIODATA("1C",[Production.Oil],"100"): returns the default value '100' if the document does not contain scenario '1C'
GETSCENARIODATABYTAG (scenariotag, variable)	Returns data for the specified variable and scenario using the scenario tag value. scenariotag: a valid (active or inactive) scenario tag. variable: a scalar or periodic variable. Example: GETSCENARIODATABYTAG("P50",[Production.Oil]) gets the value of the variable 'Production.Oil' for the scenario tagged as 'P50'. For version 16.5 Update 13 and later: the function has an optional third parameter GETSCENARIODATABYTAG(scenariotag,variable,[default]); if the requested scenario tag is not found in the document then the default value will be returned. Example: GETSCENARIODATABYTAG("P50",[Production.Oil],"100").
GETWELLPROPERTY(wellvariable, property)	Returns a text value for the specified property of the input well variable. wellvariable: a variable of data type 'Well'. property: a text string, or a ScalarString variable, or an expression that resolves to a text string. Example: GETWELLPROPERTY([WellVariable],"Field")
GETWELLRATE(variable, product, [date])	Returns periodic data for the production rate of the specified product of the input well variable. The periodicity of the data is defined by the template. The optional 'date' argument applies an offset to the data values to start at the specified date. variable: a variable of the 'Well' data type product: text string for a product, as defined in the Decline workspace. date: optional input expression that resolves to a date, either a ScalarDate variable or a DATE function. If date is omitted then the returned data begins at the start date of the well. Examples: 1. GETWELLRATE([WellVariable],"Condensate") 2. GETWELLRATE([WellVariable],"Condensate",DATE(2023,1,1)) 3. GETWELLRATE([WellVariable],"Water",[ScalarDateVariable])
GETWELLVOLUME(variable, product, [date])	Returns periodic data for the production volume of the specified product of the input well variable. The periodicity of the data is defined by the template. The optional 'date' argument applies an offset to the data values to start at the specified date. variable: a variable of the 'Well' data type product: text string for a product, as defined in the Decline workspace. date: optional input expression that resolves to a date, either a ScalarDate variable or a DATE function. If date is omitted then the returned data begins at the start date of the well. Examples: 1. GETWELLVOLUME([WellVariable],"Condensate") 2. GETWELLVOLUME([WellVariable],"Condensate",DATE(2023,1,1)) 3. GETWELLVOLUME([WellVariable],"Water",[ScalarDateVariable])
LASTNONZERODATE(periodic_variable)	Returns the last non-zero value contained in the input periodic variable, and the corresponding date. The function returns null if no non-zero values are found.
LINEITEM(variable, line_item_ID)	Returns the value of the line item in the multilevel table specified by the input variable and ID (see Tables and line items). variable: a periodic variable with associated line item(s). line_item_ID: the numeric identifier of the line item, for example 1 for the first line item.
MAXPERIODIC(periodic_variable)	Returns the maximum value contained in the input periodic variable.
NPV(rate, variable, [discountdate], [discountmethod], [donotdiscounthistoricalperiods])	Returns the net present value of an investment based on a discount rate and a series of values. Discount date and calculation method can be optionally specified. rate: a scalar value for the discount rate to be used in calculations; can be either a numerical value or a scalar variable. variable: a periodic numeric variable, usually representing a cash flow. discountdate: (optional) datetime variable or a date string to specify when NPV will be applied from; if not specified then the document start date is used. discountmethod: (optional) the discount method; the default method is 'Monthly', other available methods are: 'Annual Start', 'Annual Mid', 'Annual End'. donotdiscounthistoricalperiods (optional): boolean value to specify whether historical periods are discounted or not; default value is 'false'. Examples: 1. NPV([rate1], [cashflow1]): calculate NPV for the data in variable cashflow1 using the discount rate in variable rate1. 2. NPV("10", [cashflow2], "12/31/2022", "Annual Start", "true"): calculate NPV for cashflow2 using the discount rate 10%, discount method 'Annual Start', and historical periods will not be discounted.
NUMERICTODATE(numeric_date)	Converts the input numeric date, based on the 'OLE Automation date' format (i.e., the number of days after or before midnight on 30 December 1899), to a date string. The input value must be in the range -657435 to 2958466. Example: NUMERICTODATE(2) returns the date '1 January 1900'.
PARSEDATE(text, [format])	Converts the input text string to a date object suitable for use in date editors or functions requiring a date argument. If the optional format argument is input, the text will be parsed using this specification, otherwise the operating system regional settings will be applied. Note that the regional settings or a supplied format are always used when parsing the input text to determine a resultant date. To format a date directly as text, use the DATETEXT function instead. Examples: PARSEDATE("31 January 2010"), PARSEDATE("01/31/2010","MM/dd/yyyy") Format specifiers: d: day 1 - 31 dd: day 01 - 31 M: month 1 - 12 MM: month 01 - 12 MMM: abbreviated month name (e.g. Jan, Feb) MMMM: full month name yy: year 00 - 99 yyyy: four-digit year
PERIODICAVERAGE(variable, [periodicity])	Returns the average (arithmetic mean) of the input periodic variable over a specified periodicity. Only periods with data (including 0 values) will be evaluated. variable: a periodic variable. periodicity: optional parameter to specify the periodicity ('Annually', 'Semi-annually', 'Quarterly', 'Monthly'); if this parameter is not supplied then the document template periodicity will be used; if this is Mixed, then Annually will be used.
PRODUCTIONANALOG(variable1, variable2)	Runs a simulation using the type curve well approach. variable1: periodic variable which represents a time series curve profile, in volume per period. variable2: periodic variable which represents a drilling plan, in number of wells per period. Result: An analog style time series output in resultant volume. Example: PRODUCTIONANALOG(variable1,variable2) where variable1 (in Mbbl) = 1,2,3,5,5,4,3,2,1; variable2 (in wells) = 2,2,2,3,3,1,1,0,0. The output is (in Mbbl) = 2,6,12,23,35,44,51,53,47.
RANGE(variable, start, end)	Calculates the total value of a periodic variable for the given range. variable: periodic variable. start: range start year. end: range end year. Example: If Var1 = 1,2,3,4,5 over a period 2010 to 2014. 1. Using integer range parameters: RANGE([Var1],1,3) = 9,9,9,9,9 2. Using range parameters RANGE([Var1],"1/1/2011","12/1/2013") = 9,9,9,9,9 Note: if the function output is assigned to a scalar variable then the output is converted to a scalar value which is the sum of the calculated range values.
ROR(variable, [discountmethod])	Returns the ROR of the input periodic variable. discountmethod: optional parameter to specify the discount method ('Monthly', 'Annual Start', 'Annual Mid', 'Annual End').
TOTAL(periodic_variable)	Returns the total (sum) of all of the values contained in the input periodic variable.
VALUEBYOFFSET(variable, offset, [default])	Returns the value of the input variable for the period specified by offset. For example, an offset of -1 will return the previous period's value. default is an optional default value that will be returned when no value exists for the given offset.
VALUEBYPERIODINDEX(variable, index, [default])	Returns the value of the input variable for the period specified by the zero-based index (i.e., index 0 is the first period value). default is an optional default value that will be returned when no value exists for the given index.
VALUEBYSUBCATEGORY(variable, subcategory, [default])	Returns the value of the input variable for the index specified by subcategory. default is an optional default value that will be returned when no value is found for the given subcategory.